﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using CmuCertificates.Database;

namespace CmuCertificates
{
    public partial class MainForm : Form
    {
        private Business business = new Business();
        private IWorkbook excelData;

        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            // Import
            InitializeComboBoxChonLop();
            InitializeComboBoxChonMonHoc();
            // Manager
            openMLabelAndTextBox(new string[] { "", "", "", "", "", "" });
            // Query
            InitializeComboBoxQFCourse();
            InitializeComboBoxQFClass();
            InitializeComboBoxQFLecturer();
            InitializeComboBoxQFStudent();
            InitializeComboBoxQFYear();
            lblAutoSumCC.Text = "";
            lblSumCC.Text = "";
        }

        #region Import Feature
        // Initialize
        #region Initialize Combobox
        private void InitializeComboBoxChonLop()
        {
            // add all class name to combobox
            foreach (var item in business.SelectAll_Class())
                comboBoxChonLop.Items.Add(item.ClassName);
        }
        private void InitializeComboBoxChonMonHoc()
        {
            // Add all course short name to combobox
            foreach (var item in business.SelectAll_Courses())
            {
                comboBoxChonMonHoc.Items.Add(item.CourseShortName);
            }
        }
        #endregion
        // button click
        private void btnNhapDSSV_Click(object sender, EventArgs e)
        {
            if (comboBoxChonLop.SelectedIndex < 0)
            {
                MessageBox.Show("Bạn chưa chọn lớp");
            }
            else
            {
                OpenFileDialog openDlg = new OpenFileDialog();
                openDlg.Filter = "2007 Excel file(*.xlsx)|*.xlsx|97-2003 Excel file(*.xls)|*.xls";
                if (openDlg.ShowDialog() == DialogResult.OK)
                {
                    excelData = business.readExcelFile(openDlg.FileName);
                    string sExcelContent = ConvertExcelToText(excelData, 1); // read sheet 1
                    string[] sExcelLine = sExcelContent.Split('\n');
                    // check if excel column match with standard student column
                    string[] sCheck = sExcelLine[0].Split('|');
                    if (sCheck[0].ToLower().Trim() == "student id" &&
                        sCheck[1].ToLower().Trim() == "first name" &&
                        sCheck[2].ToLower().Trim() == "middle name" &&
                        sCheck[3].ToLower().Trim() == "last name" &&
                        sCheck[4].ToLower().Trim() == "gender" &&
                        sCheck[5].ToLower().Trim() == "birthday")
                    {
                        // can sua datagridviewAddColumn_StudentList();
                        for (int i = 1; i < sExcelLine.Length; i++)
                        {
                            #region myStudent = student to add
                            Student myStudent = new Student();
                            bool gender;
                            if (sExcelLine[i] != "")
                            {
                                string[] sSplit = sExcelLine[i].Split('|');
                                try
                                {
                                    myStudent.StudentID = sSplit[0];
                                    myStudent.FirstName = sSplit[1];
                                    myStudent.MiddleName = sSplit[2];
                                    myStudent.LastName = sSplit[3];
                                    
                                    //=========================
                                    if (sSplit[4].ToLower().Trim() == "true") gender = true;
                                    else gender = false;
                                    myStudent.Gender = gender;
                                    //=========================
                                    myStudent.Birthday = ConvertNumberToDateTime(int.Parse(sSplit[5]));
                                    //==========================
                                    myStudent.ClassID = business.Select_Class(GlobalVar.KITURONG_QUERY, comboBoxChonLop.Text, GlobalVar.KITURONG_QUERY).First().ClassID;
                                }
                                catch { }
                            #endregion
                                // check if student already exist in database
                                if (business.Select_Student(myStudent.StudentID, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY).Count() == 0
                                    && myStudent != null)
                                {
                                    business.AddStudent(myStudent);
                                }
                                else
                                    MessageBox.Show("-Sinh viên: " + myStudent.FirstName + "\n-MSSV:" + myStudent.StudentID + "\n\nĐã có trong CSDL");
                            }
                        }
                        dataGridViewCertificate.DataSource = business.GetDSSVDataTable_ImportClass(comboBoxChonLop.Text);
                    }
                    else
                    {
                        MessageBox.Show("Sai định dạng danh sách sinh viên");
                    }
                }
            }
        }
        private void btnNhapDSCC_Click(object sender, EventArgs e)
        {
            if (comboBoxChonMonHoc.SelectedIndex < 0)
            {
                MessageBox.Show("Bạn chưa chọn môn học");
            }
            else
            {
                OpenFileDialog openDlg = new OpenFileDialog();
                openDlg.Filter = "2007 Excel file(*.xlsx)|*.xlsx|97-2003 Excel file(*.xls)|*.xls";
                if (openDlg.ShowDialog() == DialogResult.OK)
                {
                    excelData = business.readExcelFile(openDlg.FileName);
                    string sExcelContent = ConvertExcelToText(excelData, 1); // read sheet 1
                    string[] sExcelLine = sExcelContent.Split('\n');
                    // check if excel column match with standard student column
                    string[] sCheck = sExcelLine[0].Split('|');
                    if (sCheck[0].ToLower().Trim() == "student id" &&
                        sCheck[1].ToLower().Trim() == "first name" &&
                        sCheck[2].ToLower().Trim() == "middle name" &&
                        sCheck[3].ToLower().Trim() == "last name" &&
                        sCheck[4].ToLower().Trim() == "gender" &&
                        sCheck[5].ToLower().Trim() == "score")
                    {
                        // can sua dataGridViewAddColumn_CertificateList();
                        for (int i = 1; i < sExcelLine.Length; i++)
                        {

                            Certificate myCertificate = new Certificate();
                            if (sExcelLine[i] != "")
                            {
                                #region myCertificate = certificate to add
                                string[] sSplit = sExcelLine[i].Split('|');
                                try
                                {
                                    //   MessageBox.Show("sSplit" + "[" + 2 + "]" + " =" + sSplit[2]);
                                    myCertificate.CertificateID = (business.SelectAll_Certificate().Count() + 1).ToString();
                                    myCertificate.StudentID = sSplit[0];
                                    //myCertificate.Student.FirstName = sSplit[1];
                                    //myCertificate.Student.MiddleName = sSplit[2];
                                    //myCertificate.Student.LastName = sSplit[3];
                                    //myCertificate.Student.Gender = sSplit[4].ToLower().Trim() == "male";
                                    //myCertificate.Student = business.Select_Student(myCertificate.StudentID, sSplit[1], sSplit[2], sSplit[3], GlobalVar.KITURONG_QUERY).First();
                                    //=========================================
                                    foreach (var item in business.SelectAll_Courses())
                                    {
                                        if (item.CourseShortName == comboBoxChonMonHoc.Text &&
                                            item.Year.Year1 == comboBoxThoiGian.Text.ToLower().Split(new String[] { ",s:" }, StringSplitOptions.RemoveEmptyEntries)[0] &&
                                            item.Year.Semester == comboBoxThoiGian.Text.ToLower().Split(new String[] { ",s:" }, StringSplitOptions.RemoveEmptyEntries)[1] &&
                                            item.Lecturer.LecturerName == comboBoxGiangVienDay.Text)
                                        {
                                            myCertificate.CourseID = item.CourseID;
                                            break;
                                        }
                                    }

                                    //=========================================
                                    myCertificate.Score = sSplit[5];
                                }
                                catch { }
                                #endregion
                                // check if certificate already exist in database
                                if (business.Select_Certificate(GlobalVar.KITURONG_QUERY, myCertificate.StudentID, myCertificate.CourseID, GlobalVar.KITURONG_QUERY).Count() == 0 && myCertificate != null)
                                {
                                    if (business.Select_Student(myCertificate.StudentID, sSplit[1], sSplit[2], sSplit[3], GlobalVar.KITURONG_QUERY).Count() == 0)
                                    {
                                        business.AddCertificate(myCertificate);
                                    }
                                    else
                                    {
                                        MessageBox.Show("-Sinh viên: " + myCertificate.StudentID + "\n\nkhông có trong danh sách sinh viên");
                                    }
                                }
                                else
                                {
                                    MessageBox.Show("-Chứng chỉ: " + myCertificate.CertificateID + "\n\nĐã có trong danh sách chứng chỉ");
                                }
                            }
                        }
                        //business.GetDSSVDataTable_ImportClass();
                        business.GetDSCCDataTable_ImportCertificate(comboBoxChonMonHoc.Text, comboBoxThoiGian.Text, comboBoxGiangVienDay.Text);
                    }
                    else
                    {
                        MessageBox.Show("Invalid certificate list");
                    }
                }
            }
        }
        private void btnXemDSSV_Click(object sender, EventArgs e)
        {
            dataGridViewCertificate.DataSource = business.GetDSSVDataTable_All();
            DataGridView_AllColumn_ReadOnly();
        }
        private void btnXemDSCC_Click(object sender, EventArgs e)
        {
            dataGridViewCertificate.DataSource = business.GetDSCCDataTable_All();
            DataGridView_AllColumn_ReadOnly();
        }

        // combobox selected index
        private void comboBoxChonMonHoc_SelectedIndexChanged(object sender, EventArgs e)
        {

            if (comboBoxChonMonHoc.SelectedIndex >= 0)
            {
                // Iinitialize
                string monhoc = comboBoxChonMonHoc.Text;
                comboBoxGiangVienDay.Items.Clear();
                comboBoxThoiGian.Items.Clear();
                // Process
                foreach (var item_Monhoc in business.SelectAll_Courses())
                {
                    if (item_Monhoc.CourseShortName == monhoc)
                    {
                        comboBoxThoiGian.Items.Add(item_Monhoc.Year.Year1 + ",S:" + item_Monhoc.Year.Semester);
                        comboBoxGiangVienDay.Items.Add(item_Monhoc.Lecturer.LecturerName);
                    }
                }
                // Default selected item index
                comboBoxGiangVienDay.Enabled = true;
                comboBoxThoiGian.Enabled = true;
                comboBoxGiangVienDay.SelectedIndex = 0;
                comboBoxThoiGian.SelectedIndex = 0;
            }
        }

        #endregion

        #region Manager
        // Initialize
        public void openMLabelAndTextBox(string[] labelText)
        {
            labelQAutoData1.Text = labelText[0]; textBoxAutoData1.Text = "";
            labelQAutoData2.Text = labelText[1]; textBoxAutoData2.Text = "";
            labelQAutoData3.Text = labelText[2]; textBoxAutoData3.Text = "";
            labelQAutoData4.Text = labelText[3]; textBoxAutoData4.Text = "";
            labelQAutoData5.Text = labelText[4]; textBoxAutoData5.Text = "";
            labelQAutoData6.Text = labelText[5]; textBoxAutoData6.Text = "";
            if (labelQAutoData1.Text == "") textBoxAutoData1.Visible = false;
            else textBoxAutoData1.Visible = true;
            if (labelQAutoData2.Text == "") textBoxAutoData2.Visible = false;
            else textBoxAutoData2.Visible = true;
            if (labelQAutoData3.Text == "") textBoxAutoData3.Visible = false;
            else textBoxAutoData3.Visible = true;
            if (labelQAutoData4.Text == "") textBoxAutoData4.Visible = false;
            else textBoxAutoData4.Visible = true;
            if (labelQAutoData5.Text == "") textBoxAutoData5.Visible = false;
            else textBoxAutoData5.Visible = true;
            if (labelQAutoData6.Text == "") textBoxAutoData6.Visible = false;
            else textBoxAutoData6.Visible = true;
        }
        // Radio button click
        private void rButtonMGiangVien_CheckedChanged(object sender, EventArgs e)
        {
            openMLabelAndTextBox(new string[] { "Tên", "", "", "", "", "" });
            if (rButtonMGiangVien.Checked)
            {
                dataGridViewCertificate.DataSource = business.GetLecturerDataTable_All();
                dataGridViewCertificate.Columns[0].Width = dataGridViewCertificate.Size.Width - 100;
            }
            DataGridView_AllColumn_ReadOnly();
            textBoxAutoData1.Text = dataGridViewCertificate.Rows[0].Cells[0].Value.ToString();
        }
        private void rButtonMLop_CheckedChanged(object sender, EventArgs e)
        {
            openMLabelAndTextBox(new string[] { "Tên", "Năm", "", "", "", "" });
            if (rButtonMLop.Checked)
                dataGridViewCertificate.DataSource = business.GetClassDataTable_All();
            DataGridView_AllColumn_ReadOnly();
            textBoxAutoData1.Text = dataGridViewCertificate.Rows[0].Cells[0].Value.ToString();
            textBoxAutoData2.Text = dataGridViewCertificate.Rows[0].Cells[1].Value.ToString();
        }
        private void rButtonMNam_CheckedChanged(object sender, EventArgs e)
        {
            openMLabelAndTextBox(new string[] { "Năm", "Học kỳ", "", "", "", "" });
            if (rButtonMNam.Checked)
                dataGridViewCertificate.DataSource = business.GetYearDataTable_All();
            DataGridView_AllColumn_ReadOnly();
            textBoxAutoData1.Text = dataGridViewCertificate.Rows[0].Cells[0].Value.ToString();
            textBoxAutoData2.Text = dataGridViewCertificate.Rows[0].Cells[1].Value.ToString();
        }
        private void rButtonMSinhVien_CheckedChanged(object sender, EventArgs e)
        {
            openMLabelAndTextBox(new string[] { "Tên", "Tên lót", "Họ", "Giới tính", "Ngày sinh", "Lớp" });
            if (rButtonMSinhVien.Checked)
                dataGridViewCertificate.DataSource = business.GetStudentDataTable_All();
            DataGridView_AllColumn_ReadOnly();
            textBoxAutoData1.Text = dataGridViewCertificate.Rows[0].Cells[0].Value.ToString();
            textBoxAutoData2.Text = dataGridViewCertificate.Rows[0].Cells[1].Value.ToString();
            textBoxAutoData3.Text = dataGridViewCertificate.Rows[0].Cells[2].Value.ToString();
            textBoxAutoData4.Text = dataGridViewCertificate.Rows[0].Cells[3].Value.ToString();
            textBoxAutoData5.Text = dataGridViewCertificate.Rows[0].Cells[4].Value.ToString();
            textBoxAutoData6.Text = dataGridViewCertificate.Rows[0].Cells[5].Value.ToString();
        }
        private void rButtonMChungChi_CheckedChanged(object sender, EventArgs e)
        {
            openMLabelAndTextBox(new string[] { "Tên", "Môn", "Điểm", "Năm", "Học kỳ", "" });
            if (rButtonMChungChi.Checked)
                dataGridViewCertificate.DataSource = business.GetCertificateDataTable_All();
            DataGridView_AllColumn_ReadOnly();
            textBoxAutoData1.Text = dataGridViewCertificate.Rows[0].Cells[0].Value.ToString();
            textBoxAutoData2.Text = dataGridViewCertificate.Rows[0].Cells[1].Value.ToString();
            textBoxAutoData3.Text = dataGridViewCertificate.Rows[0].Cells[2].Value.ToString();
            textBoxAutoData4.Text = dataGridViewCertificate.Rows[0].Cells[3].Value.ToString();
            textBoxAutoData5.Text = dataGridViewCertificate.Rows[0].Cells[4].Value.ToString();
        }
        private void rButtonMMonHoc_CheckedChanged(object sender, EventArgs e)
        {
            openMLabelAndTextBox(new string[] { "Tên MH", "Viết tắt", "Năm", "Học kỳ", "Tên GV", "" });
            if (rButtonMMonHoc.Checked)
                dataGridViewCertificate.DataSource = business.GetCourseDataTable_All();
            DataGridView_AllColumn_ReadOnly();
            textBoxAutoData1.Text = dataGridViewCertificate.Rows[0].Cells[0].Value.ToString();
            textBoxAutoData2.Text = dataGridViewCertificate.Rows[0].Cells[1].Value.ToString();
            textBoxAutoData3.Text = dataGridViewCertificate.Rows[0].Cells[2].Value.ToString();
            textBoxAutoData4.Text = dataGridViewCertificate.Rows[0].Cells[3].Value.ToString();
            textBoxAutoData5.Text = dataGridViewCertificate.Rows[0].Cells[4].Value.ToString();
        }
        private void dataGridViewCertificate_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            int rowIndex = dataGridViewCertificate.CurrentCell.RowIndex;
            if (rButtonMGiangVien.Checked)
            {
                textBoxAutoData1.Text = dataGridViewCertificate.Rows[rowIndex].Cells[0].Value.ToString();
            }
            if (rButtonMLop.Checked)
            {
                textBoxAutoData1.Text = dataGridViewCertificate.Rows[rowIndex].Cells[0].Value.ToString();
                textBoxAutoData2.Text = dataGridViewCertificate.Rows[rowIndex].Cells[1].Value.ToString();
            }
            if (rButtonMNam.Checked)
            {
                textBoxAutoData1.Text = dataGridViewCertificate.Rows[rowIndex].Cells[0].Value.ToString();
                textBoxAutoData2.Text = dataGridViewCertificate.Rows[rowIndex].Cells[1].Value.ToString();
            }
            if (rButtonMSinhVien.Checked)
            {
                textBoxAutoData1.Text = dataGridViewCertificate.Rows[rowIndex].Cells[0].Value.ToString();
                textBoxAutoData2.Text = dataGridViewCertificate.Rows[rowIndex].Cells[1].Value.ToString();
                textBoxAutoData3.Text = dataGridViewCertificate.Rows[rowIndex].Cells[2].Value.ToString();
                textBoxAutoData4.Text = dataGridViewCertificate.Rows[rowIndex].Cells[3].Value.ToString();
                textBoxAutoData5.Text = dataGridViewCertificate.Rows[rowIndex].Cells[4].Value.ToString();
                textBoxAutoData6.Text = dataGridViewCertificate.Rows[rowIndex].Cells[5].Value.ToString();
            }
            if (rButtonMChungChi.Checked)
            {
                textBoxAutoData1.Text = dataGridViewCertificate.Rows[rowIndex].Cells[0].Value.ToString();
                textBoxAutoData2.Text = dataGridViewCertificate.Rows[rowIndex].Cells[1].Value.ToString();
                textBoxAutoData3.Text = dataGridViewCertificate.Rows[rowIndex].Cells[2].Value.ToString();
                textBoxAutoData4.Text = dataGridViewCertificate.Rows[rowIndex].Cells[3].Value.ToString();
                textBoxAutoData5.Text = dataGridViewCertificate.Rows[rowIndex].Cells[4].Value.ToString();
            }
            if (rButtonMMonHoc.Checked)
            {
                textBoxAutoData1.Text = dataGridViewCertificate.Rows[rowIndex].Cells[0].Value.ToString();
                textBoxAutoData2.Text = dataGridViewCertificate.Rows[rowIndex].Cells[1].Value.ToString();
                textBoxAutoData3.Text = dataGridViewCertificate.Rows[rowIndex].Cells[2].Value.ToString();
                textBoxAutoData4.Text = dataGridViewCertificate.Rows[rowIndex].Cells[3].Value.ToString();
                textBoxAutoData5.Text = dataGridViewCertificate.Rows[rowIndex].Cells[4].Value.ToString();
            }
        }
        #endregion

        #region Query Feature
        // Initialize
        #region Initialize Combobox
        private void InitializeComboBoxQFCourse()
        {
            comboBoxQFCourse.Items.Clear();
            comboBoxQFCourse.Items.Add("None");
            foreach (var item in business.SelectAll_Courses())
                comboBoxQFCourse.Items.Add(item.CourseShortName);
        }
        private void InitializeComboBoxQFClass()
        {
            comboBoxQFClass.Items.Clear();
            comboBoxQFClass.Items.Add("None");
            foreach (var item in business.SelectAll_Class())
                comboBoxQFClass.Items.Add(item.ClassName);
        }
        private void InitializeComboBoxQFStudent()
        {
            comboBoxQFStudent.Items.Clear();
            //comboBoxQFStudent.Items.Add("None");
            //foreach (var item in business.SelectAll_Certificate())
            //    comboBoxQFStudent.Items.Add(item.StudentID);
            comboBoxQFStudent.Items.Add("All");
            comboBoxQFStudent.Items.Add("Top 10");
            foreach (var item in business.SelectAll_Class())
            {
                comboBoxQFStudent.Items.Add("Class " + item.ClassName);
            }
        }
        private void InitializeComboBoxQFLecturer()
        {
            comboBoxQFLecture.Items.Clear();
            comboBoxQFLecture.Items.Add("None");
            foreach (var item in business.SelectAll_Certificate())
                comboBoxQFLecture.Items.Add(item.Course.Lecturer.LecturerName);
        }
        private void InitializeComboBoxQFYear()
        {
            comboBoxQFYear.Items.Clear();
            comboBoxQFYear.Items.Add("None");
            foreach (var item in business.SelectAll_Certificate())
            {
                string yearFormat = item.Course.Year.Year1 + ",S:" + item.Course.Year.Semester;
                if (!comboBoxQFYear.Items.Contains(yearFormat))
                    comboBoxQFYear.Items.Add(yearFormat);
            }
        }
        #endregion

        // Button click
        private void btnQueryFilter_Click(object sender, EventArgs e)
        {
            string fCourse = comboBoxQFCourse.Text;
            string fClass = comboBoxQFClass.Text;
            string fLecture = comboBoxQFLecture.Text;
            string fYear = comboBoxQFYear.Text;
            if (fCourse == "None" || fCourse == "") fCourse = GlobalVar.KITURONG_QUERY;
            if (fClass == "None" || fClass == "") fClass = GlobalVar.KITURONG_QUERY;
            if (fLecture == "None" || fLecture == "") fLecture = GlobalVar.KITURONG_QUERY;
            if (fYear == "None" || fYear == "") fYear = GlobalVar.KITURONG_QUERY;
            dataGridViewCertificate.DataSource = business.GetDSCCDataTable_QueryCertificate(GlobalVar.KITURONG_QUERY, fCourse, fClass, fLecture, fYear);
            DataGridView_AllColumn_ReadOnly();
            // tong so chung chi
            lblAutoSumCC.Text = (dataGridViewCertificate.Rows.Count - 1).ToString();
        }
        private void btnStatistic_Click(object sender, EventArgs e)
        {
            dataGridViewCertificate.DataSource = business.GetDSCCDataTable_QueryCertificate(comboBoxQFStudent.Text, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY);
            DataGridView_AllColumn_ReadOnly();
        }
        private void tabControl_MouseClick(object sender, MouseEventArgs e)
        {
            if (tabControl.SelectedIndex != 2)
            {
                lblAutoSumCC.Text = "";
                lblSumCC.Text = "";
            }
            else

                lblSumCC.Text = "Tổng số chứng chỉ:";
            lblAutoSumCC.Text = "";
        }

        private void buttonQFXemChiTiet_Click(object sender, EventArgs e)
        {
            if (comboBoxQFStudent.Text.Contains("All"))
            {
                int valueFrom = decimal.ToInt32(numericUpDownTKCCFrom.Value);
                int valueTo = decimal.ToInt32(numericUpDownTKCCTo.Value);
                dataGridViewCertificate.DataSource = business.GetDSCCDataTable_StatisticCertificate("", false, valueFrom, valueTo);
                DataGridView_AllColumn_ReadOnly();
            }
            else if (comboBoxQFStudent.Text.Contains("Top 10"))
            {
                int valueFrom = decimal.ToInt32(numericUpDownTKCCFrom.Value);
                int valueTo = decimal.ToInt32(numericUpDownTKCCTo.Value);
                dataGridViewCertificate.DataSource = business.GetDSCCDataTable_StatisticCertificate("", true, valueFrom, valueTo);
                DataGridView_AllColumn_ReadOnly();
            }
            else
            {
                int valueFrom = decimal.ToInt32(numericUpDownTKCCFrom.Value);
                int valueTo = decimal.ToInt32(numericUpDownTKCCTo.Value);
                string inputClass = comboBoxQFStudent.Text.Replace("Class", "").Trim();
                dataGridViewCertificate.DataSource = business.GetDSCCDataTable_StatisticCertificate(inputClass, false, valueFrom, valueTo);
                DataGridView_AllColumn_ReadOnly();
            }
        }
        private void comboBoxQFStudent_SelectedIndexChanged(object sender, EventArgs e)
        {
            numericUpDownTKCCFrom.Value = 0;
            numericUpDownTKCCTo.Value = 0;
        }
        private void numericUpDownTKCCFrom_ValueChanged(object sender, EventArgs e)
        {
            if (numericUpDownTKCCFrom.Value > numericUpDownTKCCTo.Value)
            {
                numericUpDownTKCCTo.Value = numericUpDownTKCCFrom.Value;
            }
        }

        private void numericUpDownTKCCTo_ValueChanged(object sender, EventArgs e)
        {
            if (numericUpDownTKCCFrom.Value > numericUpDownTKCCTo.Value)
            {
                numericUpDownTKCCTo.Value = numericUpDownTKCCFrom.Value;
            }
        }
        #endregion

        // Utility
        private void DataGridView_AllColumn_ReadOnly()
        {
            for (int i = 0; i < dataGridViewCertificate.Columns.Count; i++)
            {
                dataGridViewCertificate.Columns[i].ReadOnly = true;
            }
        }
        private static DateTime ConvertNumberToDateTime(int input)
        {
            int year = 1900 + (input / 366);
            int month = 1;
            int dayRemain = input % 366;
            for (int i = 1900; i < year; i++)
            {
                if (!DateTime.IsLeapYear(i))
                    dayRemain++;
            }
            for (int i = 1; i <= 12; i++)
            {
                if (dayRemain - DateTime.DaysInMonth(year, i) >= 0)
                {
                    month = i;
                    dayRemain -= DateTime.DaysInMonth(year, i);
                }
                else
                    break;
            }
            dayRemain -= 1;
            month += 1;
            while (month > 12)
            {
                month -= 12;
                year++;
            }
            while (dayRemain > 31)
            {
                dayRemain -= 31;
                month++;
            }
            return new DateTime(year, month, dayRemain);
        }
        private string ConvertExcelToText(IWorkbook excelFile, int sheetIndex)
        {
            StringBuilder sOutput = new StringBuilder();
            for (int i = 0; i < sheetIndex; i++) // i < excelData.NumberOfSheets
            {
                ISheet sheet = excelFile.GetSheetAt(i);
                // read
                for (int j = 0; j <= sheet.LastRowNum; j++)
                {
                    IRow row = sheet.GetRow(j);
                    if (row != null)
                    {
                        for (int k = 0; k < row.LastCellNum; k++)
                        {
                            ICell cell = row.GetCell(k);
                            if (cell != null)
                            {
                                cell.SetCellType(CellType.String);
                                if (cell.StringCellValue != "")
                                    sOutput.Append(cell.StringCellValue + '|');
                            }
                        }
                        sOutput.AppendLine();
                    }
                }
            }
            return sOutput.ToString();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (rButtonMGiangVien.Checked)
                {
                    business.Edit_LectureTable(dataGridViewCertificate);
                }
                if (rButtonMLop.Checked)
                {
                    business.Edit_ClassTable(dataGridViewCertificate);
                }
                if (rButtonMNam.Checked)
                {
                    business.Edit_YearTable(dataGridViewCertificate);
                }
                if (rButtonMSinhVien.Checked)
                {
                    business.Edit_StudentTable(dataGridViewCertificate);
                }
                if (rButtonMChungChi.Checked)
                {
                    business.Edit_CertificateTable(dataGridViewCertificate);
                }
                if (rButtonMMonHoc.Checked)
                {
                    business.Edit_CourseTable(dataGridViewCertificate);
                }
                MessageBox.Show("Thay đổi thành công\n", "Quản lý", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch(Exception ex)
            {
                MessageBox.Show("Thay đổi thất bại\n" + ex, "Lỗi", MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
        }

        private void textBoxAutoData1_TextChanged(object sender, EventArgs e)
        {
            int rowIndex = dataGridViewCertificate.CurrentCell.RowIndex;
            dataGridViewCertificate.Rows[rowIndex].Cells[0].Value = textBoxAutoData1.Text;
        }

        private void textBoxAutoData2_TextChanged(object sender, EventArgs e)
        {
            int rowIndex = dataGridViewCertificate.CurrentCell.RowIndex;
            dataGridViewCertificate.Rows[rowIndex].Cells[1].Value = textBoxAutoData2.Text;
        }

        private void textBoxAutoData3_TextChanged(object sender, EventArgs e)
        {
            int rowIndex = dataGridViewCertificate.CurrentCell.RowIndex;
            dataGridViewCertificate.Rows[rowIndex].Cells[2].Value = textBoxAutoData3.Text;
        }

        private void textBoxAutoData4_TextChanged(object sender, EventArgs e)
        {
            int rowIndex = dataGridViewCertificate.CurrentCell.RowIndex;
            dataGridViewCertificate.Rows[rowIndex].Cells[3].Value = textBoxAutoData4.Text;
        }

        private void textBoxAutoData5_TextChanged(object sender, EventArgs e)
        {
            int rowIndex = dataGridViewCertificate.CurrentCell.RowIndex;
            dataGridViewCertificate.Rows[rowIndex].Cells[4].Value = textBoxAutoData5.Text;
        }

        private void textBoxAutoData6_TextChanged(object sender, EventArgs e)
        {
            int rowIndex = dataGridViewCertificate.CurrentCell.RowIndex;
            dataGridViewCertificate.Rows[rowIndex].Cells[5].Value = textBoxAutoData6.Text;
        }

        

    }



}
